import os
import json
import numpy as np
from flask import Flask, request, jsonify
from flask_cors import CORS
from dotenv import load_dotenv
import sqlparse
import re

# 加载环境变量
load_dotenv()

app = Flask(__name__)
CORS(app)

def load_test_data():
    """加载测试数据"""
    with open('data.json', 'r', encoding='utf-8') as f:
        return json.load(f)

def normalize_sql(sql):
    """标准化SQL语句，用于比较"""
    # 移除多余的空格和换行
    sql = re.sub(r'\s+', ' ', sql.strip())
    # 转换为小写
    sql = sql.lower()
    # 移除末尾的分号
    sql = sql.rstrip(';')
    return sql

def calculate_sql_accuracy(predicted_sql, ground_truth_sql):
    """计算SQL准确率"""
    pred_normalized = normalize_sql(predicted_sql)
    gt_normalized = normalize_sql(ground_truth_sql)
    
    # 完全匹配
    exact_match = pred_normalized == gt_normalized
    
    # 简单的语义相似度计算（基于关键词匹配）
    semantic_score = calculate_semantic_similarity(predicted_sql, ground_truth_sql)
    
    return {
        "exact_match": exact_match,
        "semantic_score": semantic_score,
        "normalized_predicted": pred_normalized,
        "normalized_ground_truth": gt_normalized
    }

def calculate_semantic_similarity(sql1, sql2):
    """计算SQL语义相似度（简化版本）"""
    # 提取SQL关键词
    keywords1 = extract_sql_keywords(sql1)
    keywords2 = extract_sql_keywords(sql2)
    
    # 计算Jaccard相似度
    intersection = len(keywords1.intersection(keywords2))
    union = len(keywords1.union(keywords2))
    
    if union == 0:
        return 1.0 if sql1.strip() == sql2.strip() else 0.0
    
    return intersection / union

def extract_sql_keywords(sql):
    """提取SQL关键词"""
    # SQL关键词列表
    sql_keywords = {
        'select', 'from', 'where', 'insert', 'update', 'delete', 'create', 'drop',
        'alter', 'table', 'database', 'index', 'view', 'procedure', 'function',
        'into', 'values', 'set', 'order', 'by', 'group', 'having', 'join',
        'inner', 'left', 'right', 'outer', 'on', 'as', 'and', 'or', 'not',
        'in', 'exists', 'between', 'like', 'is', 'null', 'distinct', 'count',
        'sum', 'avg', 'min', 'max', 'case', 'when', 'then', 'else', 'end'
    }
    
    # 转换为小写并分割
    words = re.findall(r'\b\w+\b', sql.lower())
    
    # 过滤出SQL关键词
    keywords = set(word for word in words if word in sql_keywords)
    
    # 添加表名和列名（简单提取）
    table_columns = re.findall(r'\b[a-zA-Z_][a-zA-Z0-9_]*\b', sql.lower())
    keywords.update(table_columns)
    
    return keywords

@app.route('/api/test-data', methods=['GET'])
def get_test_data():
    """获取测试数据"""
    try:
        data = load_test_data()
        return jsonify({
            "success": True,
            "data": data,
            "count": len(data)
        })
    except Exception as e:
        return jsonify({
            "success": False,
            "error": str(e)
        }), 500

@app.route('/api/evaluate-sql', methods=['POST'])
def evaluate_sql():
    """评估单个SQL语句"""
    try:
        data = request.get_json()
        predicted_sql = data.get('predicted_sql', '')
        ground_truth_sql = data.get('ground_truth_sql', '')
        question = data.get('question', '')
        
        if not predicted_sql or not ground_truth_sql:
            return jsonify({
                "success": False,
                "error": "缺少必要的SQL语句"
            }), 400
        
        # 计算SQL准确率
        sql_accuracy = calculate_sql_accuracy(predicted_sql, ground_truth_sql)
        
        return jsonify({
            "success": True,
            "sql_accuracy": sql_accuracy,
            "ragas_evaluation": None  # 简化版本不包含Ragas评估
        })
    except Exception as e:
        return jsonify({
            "success": False,
            "error": str(e)
        }), 500

@app.route('/api/evaluate-batch', methods=['POST'])
def evaluate_batch():
    """批量评估SQL语句"""
    try:
        data = request.get_json()
        evaluations = data.get('evaluations', [])
        
        if not evaluations:
            return jsonify({
                "success": False,
                "error": "缺少评估数据"
            }), 400
        
        results = []
        total_exact_matches = 0
        total_semantic_scores = []
        
        for eval_data in evaluations:
            predicted_sql = eval_data.get('predicted_sql', '')
            ground_truth_sql = eval_data.get('ground_truth_sql', '')
            question = eval_data.get('question', '')
            
            sql_accuracy = calculate_sql_accuracy(predicted_sql, ground_truth_sql)
            
            if sql_accuracy['exact_match']:
                total_exact_matches += 1
            total_semantic_scores.append(sql_accuracy['semantic_score'])
            
            results.append({
                "question": question,
                "predicted_sql": predicted_sql,
                "ground_truth_sql": ground_truth_sql,
                "sql_accuracy": sql_accuracy
            })
        
        # 计算总体统计
        exact_match_rate = total_exact_matches / len(evaluations)
        avg_semantic_score = np.mean(total_semantic_scores)
        
        return jsonify({
            "success": True,
            "results": results,
            "summary": {
                "total_evaluations": len(evaluations),
                "exact_match_rate": float(exact_match_rate),
                "average_semantic_score": float(avg_semantic_score),
                "ragas_evaluation": None  # 简化版本不包含Ragas评估
            }
        })
    except Exception as e:
        return jsonify({
            "success": False,
            "error": str(e)
        }), 500

@app.route('/api/health', methods=['GET'])
def health_check():
    """健康检查"""
    return jsonify({
        "success": True,
        "message": "Text2SQL评估服务运行正常（简化版本）"
    })

if __name__ == '__main__':
    print("🚀 启动Text2SQL评估服务（简化版本）...")
    print("📊 访问地址: http://localhost:5000")
    print("🔍 健康检查: http://localhost:5000/api/health")
    print("📋 测试数据: http://localhost:5000/api/test-data")
    app.run(debug=True, host='0.0.0.0', port=5000)
